Importing Data Through DB Scans

You can import reference data from a database by connecting the database to the application. It involves creating a reference table and adding reference columns to it. Reference column values can be imported from the desired columns in the database.

To add reference columns to reference tables, follow these steps:

  1. Go to Application Menu > Data Catalog > Reference Data Manager.
  2. Under the Workspace pane, expand the desired reference folder.
  3. Expand the Reference Tables node, right-click the desired table, and click Alter Table.

    4. The Alter Table page appears.

  4. Click .

    5. A new row is added to the grid.

  5. Double-click the cell under the Column Name and type the reference column name.

    6. Note: You can add multiple reference columns.

  6. Click .

    7. The reference columns are added to the reference table.

To add reference column values to the reference columns through a DB scan, follow these steps:

  1. Click .
  2. Click .

    3. The DB Scan page appears.

  3. Enter appropriate values in the fields in the Quick Connection tab to establish a connection with a database. Fields marked with a red asterisk are mandatory. Refer to the following table for field descriptions.

    4. Field Name

    Description

    DB Type

    Specifies the database type.

    For example, Sql Server.

    Driver Name

    Specifies the JDBC driver name for connecting to the database.

    For example, com.microsoft.sqlserver.jdbc.SQLServerDriver

    The field is autopopulated based on the DB Type.

    IP Address/Host Name

    Specifies the IP address or server host name of the database.

    For example, localhost.

    Port

    Specifies the port to connect with the database.

    For example: 1433 is the default port for a Sql Server database type.

    Database Name

    Specifies the database name being used to connect to the reference table.

    For example, ErwinDIS931.

    User Name

    Specifies the user name to connect with the database.

    For example, sa.

    Password

    Specifies the password to connect with the database.

    For example, goerwin@1.
  4. Click to test the connection.

    5. If the connection is established, the success message appears.

  5. Write a query in the Query Panel to pull the data from the database.
  6. Click to validate the query.
  7. Click to preview the data.

    8. The data is previewed.

  8. Double-click the Select Column to Import cell under the desired column.

    9. A drop-down option appears displaying the reference columns.

  9. Choose an appropriate <Reference_Column_Name> from the drop-down.

    10. Note: You can import multiple columns from the database.

  10. Click .

    11. The column values from the database are uploaded into the selected reference columns.